package com.ht.shortlink.project.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ht.shortlink.project.dao.entity.LinkAccessStatsDO;
import com.ht.shortlink.project.dto.req.ShortLinkStatsGroupReqDTO;
import com.ht.shortlink.project.dto.req.ShortLinkStatsReqDTO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 *  短链接监控访问持久层
 */
public interface LinkAccessStatsMapper extends BaseMapper<LinkAccessStatsDO> {

    /**
     *  添加或更新监控数据
     */
    @Insert("INSERT INTO t_link_access_stats(gid, full_short_url, date, pv, uv, uip, hour, weekday, create_time, update_time, del_flag) " +
            "VALUES (#{linkAccessStatsDO.gid}, #{linkAccessStatsDO.fullShortUrl}, #{linkAccessStatsDO.date}, #{linkAccessStatsDO.pv}, #{linkAccessStatsDO.uv}, #{linkAccessStatsDO.uip}, " +
            "#{linkAccessStatsDO.hour}, #{linkAccessStatsDO.weekday}, NOW(), NOW(), 0) " +
            "ON DUPLICATE KEY " +
            "UPDATE pv = pv +  #{linkAccessStatsDO.pv}," +
            "       uv = uv + #{linkAccessStatsDO.uv}," +
            "       uip = uip + #{linkAccessStatsDO.uip}," +
            "       update_time = NOW();")
    void shortLinkStats(@Param("linkAccessStatsDO") LinkAccessStatsDO linkAccessStatsDO);

    /**
     *  根据短链接查询每日基础监控数据
     */
    @Select("SELECT date,sum(pv) AS pv,sum(uv) AS uv,sum(uip) AS uip " +
            "FROM t_link_access_stats " +
            "WHERE full_short_url=#{param.fullShortUrl} AND gid=#{param.gid} AND date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY full_short_url,date,gid;")
    List<LinkAccessStatsDO> listStatsByLink(@Param("param") ShortLinkStatsReqDTO param);

    /**
     *  根据分组查询每日基础监控数据
     */
    @Select("SELECT date,sum(pv) AS pv,sum(uv) AS uv,sum(uip) AS uip " +
            "FROM t_link_access_stats " +
            "WHERE gid=#{param.gid} " +
            "AND date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY gid,date;")
    List<LinkAccessStatsDO> listStatsByGroup(@Param("param") ShortLinkStatsGroupReqDTO param);

    /**
     *  根据短链接查询每小时基础监控数据
     */
    @Select("SELECT hour,sum(pv) AS pv " +
            "FROM t_link_access_stats " +
            "WHERE full_short_url=#{param.fullShortUrl} AND gid=#{param.gid} AND date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY full_short_url,hour,gid;")
    List<LinkAccessStatsDO> listStatsHourByLink(@Param("param") ShortLinkStatsReqDTO param);

    /**
     *  根据分组查询每小时基础监控数据
     */
    @Select("SELECT hour,sum(pv) AS pv " +
            "FROM t_link_access_stats " +
            "WHERE gid=#{param.gid} AND date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY hour,gid;")
    List<LinkAccessStatsDO> listStatsHourByGroup(@Param("param") ShortLinkStatsGroupReqDTO requestParam);

    /**
     *  根据短链接查询一周每一天基础监控数据
     */
    @Select("SELECT weekday,sum(pv) AS pv " +
            "FROM t_link_access_stats " +
            "WHERE full_short_url=#{param.fullShortUrl} AND gid=#{param.gid} AND date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY full_short_url,weekday,gid;")
    List<LinkAccessStatsDO> listStatsWeekdayByLink(@Param("param") ShortLinkStatsReqDTO param);

    /**
     *  根据分组查询一周每一天基础监控数据
     */
    @Select("SELECT weekday,sum(pv) AS pv " +
            "FROM t_link_access_stats " +
            "WHERE gid=#{param.gid} AND date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY gid,weekday;")
    List<LinkAccessStatsDO> listStatsWeekdayByGroup(@Param("param") ShortLinkStatsGroupReqDTO requestParam);



}
